﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MSWord = Microsoft.Office.Interop.Word;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using Microsoft.Office.Interop.Word;
using DBUtility;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace HXSH5._0
{
    class CreateWord
    {
        string strContent;//文件内容
        //protected static string connectionString = @"Data Source=192.168.1.214;Initial Catalog=HSXH;User ID=sa;Password=Ningtu2015";
        private static string connectionString = @"Server=DESKTOP-RR1VURH\SQLEXPRESS;Database =HXSH;Trusted_Connection=True";

        public void CreatWorkExplain(string brake, DateTime date)
        {
            object path;//文件路径
            MSWord.Application wordApp;//Word应用程序变量
            MSWord.Document wordDoc;//Word文档变量
            path = "d:\\垂直位移观测成果表.doc";//保存为Word2003文档
            // path = "d:\\myWord.doc";//保存为Word2007文档
            wordApp = new MSWord.ApplicationClass();//初始化
            object unite = Microsoft.Office.Interop.Word.WdUnits.wdStory;
            if (File.Exists((string)path))
            {
                File.Delete((string)path);
            }
            Object Nothing = Missing.Value;
            wordDoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);
            CZWYGCB(unite, Nothing, wordApp, wordDoc, brake, date);
            //关闭保存word
            object format = MSWord.WdSaveFormat.wdFormatDocument;
            wordDoc.SaveAs(ref path, ref format, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
            wordDoc.Close(ref Nothing, ref Nothing, ref Nothing);
            wordApp.Quit(ref Nothing, ref Nothing, ref Nothing); //关闭wordApp组件对象
            DialogResult dr = MessageBox.Show("导出至D盘根目录成功！是否打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
            if (dr == DialogResult.Yes)
            {
                System.Diagnostics.Process.Start(path.ToString()); //打开此文件
            }
            
        }
        /// <summary>
        /// 垂直位移观测成果表Word设计
        /// </summary>
        /// <param name="unite"></param>
        /// <param name="Nothing"></param>
        /// <param name="wordApp"></param>
        /// <param name="wordDoc"></param>
        /// <param name="brake">闸站名</param>
        /// <param name="date">观测日期</param>
        public void CZWYGCB(object unite, object Nothing, MSWord.Application wordApp, MSWord.Document wordDoc, string brake, DateTime date)
        {
            object mymissing = System.Reflection.Missing.Value;
            object myunit = Microsoft.Office.Interop.Word.WdUnits.wdStory;
            object pBreak = (int)Microsoft.Office.Interop.Word.WdBreakType.wdPageBreak;
            //换页
            wordApp.Selection.EndKey(ref myunit, ref mymissing);
            strContent = brake + "垂直位移观测成果表\n ";//标题，在文本中使用'\n'换行
            wordDoc.Paragraphs.Last.Range.Font.Name = "宋体";
            wordDoc.Paragraphs.Last.Range.Font.Size = 20;//字体大小
            wordDoc.Paragraphs.Last.Range.Font.Bold = 1;//Bold=0为不加粗
            wordDoc.Paragraphs.Last.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
            wordDoc.Paragraphs.Last.Range.Text = strContent;
            wordApp.Selection.EndKey(ref unite, ref Nothing);
            //获取表格的行数，表头有三行！
            string SQL = "select CdPart ,CdId,Hit from [cd_result] where ID=(select ID from [cd_observation] where BrakeId ='" + brake + "'and Date='" + date + "')";
            System.Data.DataTable Dt = GetDataTable(SQL);
            int tableRow = Dt.Rows.Count + 3; 
            int tableColumn = 8;
            //定义一个word中的表格对象
            MSWord.Table table = wordDoc.Tables.Add(wordApp.Selection.Range, tableRow, tableColumn, ref Nothing, ref Nothing);
            //通过闸站名和日期获取ID，进一步查询高程
            string sql0 = "select min(Date) from [cd_observation] where BrakeId = '" + brake + "'";
            System.Data.DataTable dt0 = GetDataTable(sql0);
            string sql00 = "select ID from [cd_observation] where BrakeId = '" + brake + "' and Date = '" + Convert.ToDateTime(dt0.Rows[0][0]) + "'";
            System.Data.DataTable dt00 = GetDataTable(sql00);
            string firstid = dt00.Rows[0][0].ToString();
            string sql = "select ID,Date from [cd_observation] where Date = '" + date + "' and BrakeId = '" + brake + "'";
            System.Data.DataTable dt = GetDataTable(sql);
            string id = dt.Rows[0][0].ToString();
            string lastid;
            DateTime lastdate = MyMeans.GetFirstLastDate("cd_observation", "Date", date, "BrakeId", brake, "ID", out lastid);
            string sql1 = "select CdPart,CdId,Hit from [cd_result] where ID = '" + id + "' order by CdPart,CdId";
            System.Data.DataTable dt1 = GetDataTable(sql1);
            string sql2 = "select Hit from [cd_result] where ID = '" + firstid + "' order by CdPart,CdId";
            System.Data.DataTable dt2 = GetDataTable(sql2);
            string sql3 = "select Hit from [cd_result] where ID = '" + lastid + "' order by CdPart,CdId";
            System.Data.DataTable dt3 = GetDataTable(sql3);
            //表格的索引是从1开始的
            table.Cell(1, 1).Range.Text = "始测日期";
            table.Cell(1, 3).Range.Text = Convert.ToDateTime(dt0.Rows[0][0]).ToLongDateString();
            table.Cell(1, 4).Range.Text = "上次观测日期";
            table.Cell(1, 5).Range.Text = lastdate.ToLongDateString();
            table.Cell(1, 6).Range.Text = "本次观测日期";
            table.Cell(1, 7).Range.Text = date.ToLongDateString();
            TimeSpan t = date - lastdate;
            int days = t.Days;
            //表头三行
            table.Cell(1, 8).Range.Text = "间隔" + days + "天";
            table.Cell(2, 1).Range.Text = "测点";
            table.Cell(2, 3).Range.Text = "始测高程（米）";
            table.Cell(2, 4).Range.Text = "上次观测高程（米）";
            table.Cell(2, 5).Range.Text = "本次观测高程（米）";
            table.Cell(2, 6).Range.Text = "间隔位移量（毫米）";
            table.Cell(2, 7).Range.Text = "累计位移量（毫米）";
            table.Cell(2, 8).Range.Text = "备注";
            table.Cell(3, 1).Range.Text = "部位";
            table.Cell(3, 2).Range.Text = "编号";
            //写入表格主内容
            for (int r = 4; r <= tableRow; r++)
            {
                table.Cell(r, 1).Range.Text = dt1.Rows[r - 4][0].ToString();
                table.Cell(r, 2).Range.Text = dt1.Rows[r - 4][1].ToString();
                table.Cell(r, 3).Range.Text = dt2.Rows[r - 4][0].ToString();
                table.Cell(r, 4).Range.Text = dt3.Rows[r - 4][0].ToString();
                table.Cell(r, 5).Range.Text = dt1.Rows[r - 4][2].ToString();
                table.Cell(r, 6).Range.Text = (Math.Round((Convert.ToDouble(dt1.Rows[r - 4][2]) - Convert.ToDouble(dt3.Rows[r - 4][0])), 4) * 1000).ToString();
                table.Cell(r, 7).Range.Text = (Math.Round((Convert.ToDouble(dt1.Rows[r - 4][2]) - Convert.ToDouble(dt2.Rows[r - 4][0])), 4) * 1000).ToString();
            } 
            //设置table字体样式
            table.Rows.HeightRule = MSWord.WdRowHeightRule.wdRowHeightAtLeast;//列高度至少会是指定的最小值。
            table.Rows.Height = wordApp.CentimetersToPoints(float.Parse("0.8"));//将计量单位从厘米转换为磅（一磅等于 0.035 厘米）
            table.Range.Font.Size = 10.5F;
            table.Range.Font.Bold = 0;
            //设置table位置
            table.Rows.Alignment = WdRowAlignment.wdAlignRowCenter;//表格居中
            table.Range.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphCenter;//表格文本居中
            table.Range.Cells.VerticalAlignment = MSWord.WdCellVerticalAlignment.wdCellAlignVerticalCenter;//文本垂直贴到底部
            //设置table边框为单线
            table.Borders.OutsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;
            table.Borders.InsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;
            //所有单元格的高度
            table.Rows[1].Height = 40;
            //除第一行外，其他行的行高都设置为20
            for (int i = 2; i <= tableRow; i++)
            {
                table.Rows[i].Height = 20;
            }
            table.Columns[1].Width = 60;//将第1列宽度设置为60
            table.Columns[2].Width = 40;//将第2列宽度设置为40
            //将其他列的宽度都设置为60
            for (int i = 3; i <= tableColumn - 1; i++)
            {
                table.Columns[i].Width = 60;
            }
            //合并单元格
            table.Cell(1, 1).Merge(table.Cell(1, 2));//横向合并
            table.Cell(2, 1).Merge(table.Cell(2, 2));
            table.Cell(2, 2).Merge(table.Cell(3, 3));//纵向合并
            table.Cell(2, 3).Merge(table.Cell(3, 4));
            table.Cell(2, 4).Merge(table.Cell(3, 5));
            table.Cell(2, 5).Merge(table.Cell(3, 6));
            table.Cell(2, 6).Merge(table.Cell(3, 7));
            table.Cell(2, 7).Merge(table.Cell(3, 8));
        }

        public void CreatWorkExplain2(string brake, int year)
        {
            object path;//文件路径
            MSWord.Application wordApp;//Word应用程序变量
            MSWord.Document wordDoc;//Word文档变量
            path = "d:\\垂直位移工作基点高程考证表.doc";//保存为Word2003文档
            // path = "d:\\myWord.doc";//保存为Word2007文档
            wordApp = new MSWord.ApplicationClass();//初始化
            object unite = Microsoft.Office.Interop.Word.WdUnits.wdStory;
            if (File.Exists((string)path))
            {
                File.Delete((string)path);
            }
            Object Nothing = Missing.Value;
            wordDoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);
            JDGCKZB(unite, Nothing, wordApp, wordDoc, brake,year);
            //关闭保存word
            object format = MSWord.WdSaveFormat.wdFormatDocument;
            wordDoc.SaveAs(ref path, ref format, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
            wordDoc.Close(ref Nothing, ref Nothing, ref Nothing);
            wordApp.Quit(ref Nothing, ref Nothing, ref Nothing); //关闭wordApp组件对象
            DialogResult dr = MessageBox.Show("导出至D盘根目录成功！是否打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
            if (dr == DialogResult.Yes)
            {
                System.Diagnostics.Process.Start(path.ToString()); //打开此文件
            }
        }
        /// <summary>
        /// 工作基点高程考证表word设计
        /// </summary>
        /// <param name="unite"></param>
        /// <param name="Nothing"></param>
        /// <param name="wordApp"></param>
        /// <param name="wordDoc"></param>
        /// <param name="brake">闸站名</param>
        /// <param name="year">年份</param>
        public void JDGCKZB(object unite, object Nothing, MSWord.Application wordApp, MSWord.Document wordDoc, string brake, int year)
        {
            object mymissing = System.Reflection.Missing.Value;
            object myunit = Microsoft.Office.Interop.Word.WdUnits.wdStory;
            object pBreak = (int)Microsoft.Office.Interop.Word.WdBreakType.wdPageBreak;
            //换页
            wordApp.Selection.EndKey(ref myunit, ref mymissing);
            //wordApp.Selection.InsertBreak(ref pBreak);
            strContent = brake + "垂直位移工作基点高程考证表\n ";//在文本中使用'\n'换行
            wordDoc.Paragraphs.Last.Range.Font.Name = "宋体";
            wordDoc.Paragraphs.Last.Range.Font.Size = 20;
            wordDoc.Paragraphs.Last.Range.Font.Bold = 1;//Bold=0为不加粗
            wordDoc.Paragraphs.Last.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
            wordDoc.Paragraphs.Last.Range.Text = strContent;
            wordApp.Selection.EndKey(ref unite, ref Nothing);
            //把年份加1才能选出今年的观测日期
            int maxYear = year + 1;
            //根据闸站获取所有基点（排序）
            string sql0 = "select distinct JdId from [jd_confirm] where BrakeId = '" + brake + "' order by JdId";
            DataSet ds0 = DbHelperSQL.Query(sql0);
            string[] jd = new string[ds0.Tables[0].Rows.Count];//基点编号
            DateTime[] firstdate = new DateTime[ds0.Tables[0].Rows.Count];//首次观测日期
            double[] firsthit = new double[ds0.Tables[0].Rows.Count];//首次观测高程
            DateTime[] lastdate = new DateTime[ds0.Tables[0].Rows.Count];//上次观测日期
            double[] lasthit = new double[ds0.Tables[0].Rows.Count];//上次观测高程
            DateTime[] date = new DateTime[ds0.Tables[0].Rows.Count];//本次观测日期
            double[] hit = new double[ds0.Tables[0].Rows.Count];//本次观测高程
            for (int i = 0; i < ds0.Tables[0].Rows.Count; i++)
            {
                //把基点读进数组
                jd[i] = ds0.Tables[0].Rows[i][0].ToString();
                //把首次观测日期读进数组
                string sql00 = "select min(JdConfirmDate) from [jd_confirm] where BrakeId = '" + brake + "' and JdId = '" + ds0.Tables[0].Rows[i][0].ToString() + "'";
                DataSet ds00 = DbHelperSQL.Query(sql00);
                firstdate[i] = Convert.ToDateTime(ds00.Tables[0].Rows[0][0]);
                //把首次观测高程读进数组
                string sql000 = "select JdConfirmHit from [jd_confirm] where BrakeId = '" + brake + "' and JdId = '" + ds0.Tables[0].Rows[i][0].ToString() + "' and JdConfirmDate = '" + firstdate[i] + "'";
                DataSet ds000 = DbHelperSQL.Query(sql000);
                firsthit[i] = Convert.ToDouble(ds000.Tables[0].Rows[0][0]);
                //把上次观测日期、本次观测日期读进数组
                string sql = "select JdConfirmDate from [jd_confirm] where BrakeId = '" + brake + "' and JdId = '" + ds0.Tables[0].Rows[i][0].ToString() + "' and JdConfirmDate < '" + maxYear + "' order by JdConfirmDate desc";
                DataSet ds = DbHelperSQL.Query(sql);
                lastdate[i] = Convert.ToDateTime(ds.Tables[0].Rows[1][0]);
                date[i] = Convert.ToDateTime(ds.Tables[0].Rows[0][0]);
                //把上次观测高程读进数组
                string sql1 = "select JdConfirmHit from [jd_confirm] where BrakeId = '" + brake + "' and JdId = '" + ds0.Tables[0].Rows[i][0].ToString() + "' and JdConfirmDate = '" + lastdate[i] + "'";
                DataSet ds1 = DbHelperSQL.Query(sql1);
                lasthit[i] = Convert.ToDouble(ds1.Tables[0].Rows[0][0]);
                //把本次观测高程读进数组
                string sql2 = "select JdConfirmHit from [jd_confirm] where BrakeId = '" + brake + "' and JdId = '" + ds0.Tables[0].Rows[i][0].ToString() + "' and JdConfirmDate = '" + date[i] + "'";
                DataSet ds2 = DbHelperSQL.Query(sql2);
                hit[i] = Convert.ToDouble(ds2.Tables[0].Rows[0][0]);
            }
            //插入表
            int tableRow = ds0.Tables[0].Rows.Count + 2;
            int tableColumn = 8;
            //定义一个word中的表格对象
            MSWord.Table table = wordDoc.Tables.Add(wordApp.Selection.Range, tableRow, tableColumn, ref Nothing, ref Nothing);
            //表格的索引是从1开始的
            table.Cell(1, 1).Range.Text = "基点编号";
            table.Cell(1, 2).Range.Text = "原始观测";
            table.Cell(1, 4).Range.Text = "上次观测";
            table.Cell(1, 6).Range.Text = "本次观测";
            table.Cell(1, 8).Range.Text = "备注";
            table.Cell(2, 2).Range.Text = "观测日期";
            table.Cell(2, 3).Range.Text = "高程";
            table.Cell(2, 4).Range.Text = "观测日期";
            table.Cell(2, 5).Range.Text = "高程";
            table.Cell(2, 6).Range.Text = "观测日期";
            table.Cell(2, 7).Range.Text = "高程";
            //写入表格主内容
            for (int i = 3; i <= tableRow; i++)
            {
                table.Cell(i, 1).Range.Text = jd[i-3].ToString();
                table.Cell(i, 2).Range.Text = firstdate[i-3].ToShortDateString();
                table.Cell(i, 3).Range.Text = firsthit[i-3].ToString();
                table.Cell(i, 4).Range.Text = lastdate[i - 3].ToShortDateString();
                table.Cell(i, 5).Range.Text = lasthit[i-3].ToString();
                table.Cell(i, 6).Range.Text = date[i - 3].ToShortDateString();
                table.Cell(i, 7).Range.Text = hit[i-3].ToString();
            }
            //设置table字体样式
            table.Rows.HeightRule = MSWord.WdRowHeightRule.wdRowHeightAtLeast;//列高度至少会是指定的最小值。
            table.Rows.Height = wordApp.CentimetersToPoints(float.Parse("0.8"));//将计量单位从厘米转换为磅（一磅等于 0.035 厘米）
            table.Range.Font.Size = 10.5F;
            table.Range.Font.Bold = 0;
            //设置table位置
            table.Rows.Alignment = WdRowAlignment.wdAlignRowCenter;//表格居中
            table.Range.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphCenter;//表格文本居中
            table.Range.Cells.VerticalAlignment = MSWord.WdCellVerticalAlignment.wdCellAlignVerticalCenter;//文本垂直贴到底部
            //设置table边框样式
            table.Borders.OutsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;
            table.Borders.InsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;
            //所有行行高设为20
            for (int i = 1; i <= tableRow; i++)
            {
                table.Rows[i].Height = 20;
            }
            table.Columns[1].Width = 40;//第一列宽度设为40
            //将其他列的宽度都设置为65
            for (int i = 2; i <= tableColumn; i++)
            {
                table.Columns[i].Width = 65;
            }
            //合并单元格
            table.Cell(1, 2).Merge(table.Cell(1, 3));//横向合并
            table.Cell(1, 3).Merge(table.Cell(1, 4));//横向合并
            table.Cell(1, 4).Merge(table.Cell(1, 5));//横向合并
            table.Cell(1, 1).Merge(table.Cell(2, 1));//纵向合并
        }

        public void CreatWorkExplain3(string brake)
        {
            object path;//文件路径
            MSWord.Application wordApp;//Word应用程序变量
            MSWord.Document wordDoc;//Word文档变量
            path = "d:\\垂直位移工作基点考证表.doc";//保存为Word2003文档
            // path = "d:\\myWord.doc";//保存为Word2007文档
            wordApp = new MSWord.ApplicationClass();//初始化
            object unite = Microsoft.Office.Interop.Word.WdUnits.wdStory;
            if (File.Exists((string)path))
            {
                File.Delete((string)path);
            }
            Object Nothing = Missing.Value;
            wordDoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);
            CZWYGZJDKZB(unite, Nothing, wordApp, wordDoc, brake);
            //关闭保存word
            object format = MSWord.WdSaveFormat.wdFormatDocument;
            wordDoc.SaveAs(ref path, ref format, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
            wordDoc.Close(ref Nothing, ref Nothing, ref Nothing);
            wordApp.Quit(ref Nothing, ref Nothing, ref Nothing); //关闭wordApp组件对象
            DialogResult dr = MessageBox.Show("导出至D盘根目录成功！是否打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
            if (dr == DialogResult.Yes)
            {
                System.Diagnostics.Process.Start(path.ToString()); //打开此文件
            }
        }
        /// <summary>
        /// 工作基点考证表word设计
        /// </summary>
        /// <param name="unite"></param>
        /// <param name="Nothing"></param>
        /// <param name="wordApp"></param>
        /// <param name="wordDoc"></param>
        /// <param name="brake"></param>
        public void CZWYGZJDKZB(object unite, object Nothing, MSWord.Application wordApp, MSWord.Document wordDoc, string brake)
        {
            object mymissing = System.Reflection.Missing.Value;
            object myunit = Microsoft.Office.Interop.Word.WdUnits.wdStory;
            object pBreak = (int)Microsoft.Office.Interop.Word.WdBreakType.wdPageBreak;
            //换页
            wordApp.Selection.EndKey(ref myunit, ref mymissing);
            strContent = brake + "垂直位移工作基点考证表\n ";//在文本中使用'\n'换行
            wordDoc.Paragraphs.Last.Range.Font.Name = "宋体";
            wordDoc.Paragraphs.Last.Range.Font.Size = 20;
            wordDoc.Paragraphs.Last.Range.Font.Bold = 1;//Bold=0为不加粗
            wordDoc.Paragraphs.Last.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
            wordDoc.Paragraphs.Last.Range.Text = strContent;
            wordApp.Selection.EndKey(ref unite, ref Nothing);
            //绑数据
            string sql = "select jd_info.JdId ,jd_info.JdMat,jd_info.JdDate,jd_info.JdPos ,jd_info.JdSit ,jd_confirm.JdConfirmDate ,jd_confirm.JdConfirmHit  from jd_info ,jd_confirm where jd_info.BrakeId = jd_confirm.BrakeId and jd_confirm.BrakeId='" + brake + "'and jd_info.JdId=jd_confirm.JdId order by jd_info.JdId";
            System.Data.DataTable dt = GetDataTable(sql);
            //插入表
            int tableRow = dt.Rows.Count + 1;
            int tableColumn = 8;
            //定义一个word中的表格对象
            MSWord.Table table = wordDoc.Tables.Add(wordApp.Selection.Range, tableRow, tableColumn, ref Nothing, ref Nothing);
            //表格的索引是从1开始的
            table.Cell(1, 1).Range.Text = "基点编号";
            table.Cell(1, 2).Range.Text = "标点材料";
            table.Cell(1, 3).Range.Text = "埋设日期";
            table.Cell(1, 4).Range.Text = "位置";
            table.Cell(1, 5).Range.Text = "地基情况";
            table.Cell(1, 6).Range.Text = "考证日期";
            table.Cell(1, 7).Range.Text = "高程m";
            table.Cell(1, 8).Range.Text = "备注";
            //写入表格主内容
            for (int i = 2; i <= tableRow; i++)
            {
                for (int j = 1; j <= tableColumn - 1; j++)//因为第8列备注为空
                {
                    if (j == 3 || j == 6)
                    {
                        table.Cell(i, j).Range.Text = Convert.ToDateTime(dt.Rows[i - 2][j - 1]).ToShortDateString();//日期列
                    }
                    else if (j == 7)
                    {
                        table.Cell(i, j).Range.Text = Convert.ToDouble(dt.Rows[i - 2][6]).ToString();//高程列
                    }
                    else
                    {
                        table.Cell(i, j).Range.Text = dt.Rows[i - 2][j - 1].ToString();//其余列
                    }
                }
            }
            //设置table字体样式
            table.Rows.HeightRule = MSWord.WdRowHeightRule.wdRowHeightAtLeast;//列高度至少会是指定的最小值。
            table.Rows.Height = wordApp.CentimetersToPoints(float.Parse("0.8"));//将计量单位从厘米转换为磅（一磅等于 0.035 厘米）
            table.Range.Font.Size = 10.5F;
            table.Range.Font.Bold = 0;
            //设置table位置
            table.Rows.Alignment = WdRowAlignment.wdAlignRowCenter;//表格居中
            table.Range.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphCenter;//表格文本居中
            table.Range.Cells.VerticalAlignment = MSWord.WdCellVerticalAlignment.wdCellAlignVerticalCenter;//文本垂直贴到底部
            //设置table边框样式单线
            table.Borders.OutsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;
            table.Borders.InsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;
            //所有行行高设为20
            for (int i = 1; i <= tableRow; i++)
            {
                table.Rows[i].Height = 20;
            }
            table.Columns[1].Width = 40;//第一列宽度设为40
            //将其他列的宽度都设置为65
            for (int i = 2; i <= tableColumn; i++)
            {
                table.Columns[i].Width = 65;
            }
        }

        public void CreatWorkExplain4(string brake, DateTime date, double firsthit)
        {
            object path;//文件路径
            MSWord.Application wordApp;//Word应用程序变量
            MSWord.Document wordDoc;//Word文档变量
            path = "d:\\垂直位移观测记录表.doc";//保存为Word2003文档
            // path = "d:\\myWord.doc";//保存为Word2007文档
            wordApp = new MSWord.ApplicationClass();//初始化
            object unite = Microsoft.Office.Interop.Word.WdUnits.wdStory;
            if (File.Exists((string)path))
            {
                File.Delete((string)path);
            }
            Object Nothing = Missing.Value;
            wordDoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);
            CZWYGCJLB(unite, Nothing, wordApp, wordDoc, brake, date,firsthit);
            //关闭保存word
            object format = MSWord.WdSaveFormat.wdFormatDocument;
            wordDoc.SaveAs(ref path, ref format, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
            wordDoc.Close(ref Nothing, ref Nothing, ref Nothing);
            wordApp.Quit(ref Nothing, ref Nothing, ref Nothing); //关闭wordApp组件对象
            DialogResult dr = MessageBox.Show("导出至D盘根目录成功！是否打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
            if (dr == DialogResult.Yes)
            {
                System.Diagnostics.Process.Start(path.ToString()); //打开此文件
            }
        }
        /// <summary>
        /// 垂直位移观测记录表word设计
        /// </summary>
        /// <param name="unite"></param>
        /// <param name="Nothing"></param>
        /// <param name="wordApp"></param>
        /// <param name="wordDoc"></param>
        /// <param name="str1">闸站名</param>
        /// <param name="str2">观测日期</param>
        public void CZWYGCJLB(object unite, object Nothing, MSWord.Application wordApp, MSWord.Document wordDoc, string brake, DateTime date, double firsthit)
        {
            object mymissing = System.Reflection.Missing.Value;
            object myunit = Microsoft.Office.Interop.Word.WdUnits.wdStory;
            object pBreak = (int)Microsoft.Office.Interop.Word.WdBreakType.wdPageBreak;
            strContent = brake + "垂直位移观测记录表\n ";//在文本中使用'\n'换行
            wordDoc.Paragraphs.Last.Range.Font.Name = "宋体";
            wordDoc.Paragraphs.Last.Range.Font.Size = 20;
            wordDoc.Paragraphs.Last.Range.Font.Bold = 1;//Bold=0为不加粗
            wordDoc.Paragraphs.Last.Alignment = WdParagraphAlignment.wdAlignParagraphCenter;
            wordDoc.Paragraphs.Last.Range.Text = strContent;
            wordApp.Selection.EndKey(ref unite, ref Nothing);
            //插入表
            string SQL = "select  Number, BackPoint, FrontPoint, BackDis1, FrontDis1, FrontDis2, BackDis2, DisDiff, BackData1, FrontData1, FrontData2, BackData2, DisDiffSum, ReadDiff,Hitdiff1, Hitdiff2,Hitdiff,Hit,PHit from cd_observe where ID=(select ID from cd_observation where BrakeId='" + brake + "'and Date='" + date + "')";
            System.Data.DataTable dt1 = DbHelperSQL.GetDataTable(SQL);
            string str = "select  Number from cd_observe where ID=(select ID from cd_observation where BrakeId='" + brake + "'and Date='" + date + "')and Number=0";
            System.Data.DataTable dt2 = DbHelperSQL.GetDataTable(str);
            int a = (Convert.ToInt32(dt1.Rows.Count) - Convert.ToInt32(dt2.Rows.Count)) * 3 + Convert.ToInt32(dt2.Rows.Count);
            int tableRow = a + 4;
            int tableColumn = 10;
            //定义一个word中的表格对象
            MSWord.Table table = wordDoc.Tables.Add(wordApp.Selection.Range, tableRow, tableColumn, ref Nothing, ref Nothing);
            //表格的索引是从1开始的
            table.Cell(1, 1).Range.Text = "测站";
            table.Cell(1, 2).Range.Text = "视准点";
            table.Cell(2, 2).Range.Text = "后视";
            table.Cell(3, 2).Range.Text = "前视";
            table.Cell(4, 2).Range.Text = "中视";
            table.Cell(1, 3).Range.Text = "视距读数";
            table.Cell(2, 3).Range.Text = "后距1";
            table.Cell(2, 4).Range.Text = "后距2";
            table.Cell(3, 3).Range.Text = "前距1";
            table.Cell(3, 4).Range.Text = "前距2";
            table.Cell(4, 3).Range.Text = "视距差(m)";
            table.Cell(4, 4).Range.Text = "累计差(m)";

            table.Cell(1, 5).Range.Text = "标尺读数";
            table.Cell(2, 5).Range.Text = "后尺读数1";
            table.Cell(2, 6).Range.Text = "后尺读数2";
            table.Cell(3, 5).Range.Text = "前尺读数1";
            table.Cell(3, 6).Range.Text = "前尺读数2";
            table.Cell(4, 5).Range.Text = "高差(m)";
            table.Cell(4, 6).Range.Text = "高差(m)";

            table.Cell(1, 7).Range.Text = "读数差(mm)";
            table.Cell(1, 8).Range.Text = "高差(m)";
            table.Cell(1, 9).Range.Text = "高程(m)";
            table.Cell(1, 10).Range.Text = "平差后高程(m)";

            table.Cell(5, 9).Range.Text = firsthit.ToString();
            int j = 0;
            for (int i = 5; i <= tableRow; i++)
            {

                if (Convert.ToInt32(dt1.Rows[j][0]) == 0)//特殊情况的测站
                {
                    table.Cell(i, 2).Range.Text = (string)dt1.Rows[j][2];//前视点
                    table.Cell(i, 3).Range.Text = ((double)dt1.Rows[j][4]).ToString();//前距1
                    table.Cell(i, 5).Range.Text = ((double)dt1.Rows[j][9]).ToString();//前尺度数1
                    table.Cell(i, 9).Range.Text = ((double)dt1.Rows[j][17]).ToString();//高程
                    table.Cell(i, 10).Range.Text = ((double)dt1.Rows[j][18]).ToString();//平差后高程
                    j += 1;
                }
                else
                {
                    table.Cell(i, 1).Merge(table.Cell(i + 2, 1));

                    table.Cell(i, 1).Range.Text = Convert.ToString(dt1.Rows[j][0]);
                    table.Cell(i, 2).Range.Text = (string)dt1.Rows[j][1];//后视点
                    table.Cell(i + 1, 2).Range.Text = (string)dt1.Rows[j][2];//前视点

                    table.Cell(i, 3).Range.Text = ((double)dt1.Rows[j][3]).ToString();//后距1
                    table.Cell(i + 1, 3).Range.Text = ((double)dt1.Rows[j][4]).ToString();//前距1
                    table.Cell(i + 2, 3).Range.Text = ((double)dt1.Rows[j][7]).ToString();//视距差

                    table.Cell(i, 4).Range.Text = ((double)dt1.Rows[j][6]).ToString();//后距2
                    table.Cell(i + 1, 4).Range.Text = ((double)dt1.Rows[j][5]).ToString();//前距2
                    table.Cell(i + 2, 4).Range.Text = ((double)dt1.Rows[j][12]).ToString();//累计差

                    table.Cell(i, 5).Range.Text = ((double)dt1.Rows[j][8]).ToString();//后尺度数1
                    table.Cell(i + 1, 5).Range.Text = ((double)dt1.Rows[j][9]).ToString();//前尺度数1
                    table.Cell(i + 2, 5).Range.Text = ((double)dt1.Rows[j][14]).ToString();//高差1

                    table.Cell(i, 6).Range.Text = ((double)dt1.Rows[j][11]).ToString();//后尺度数2
                    table.Cell(i + 1, 6).Range.Text = ((double)dt1.Rows[j][10]).ToString();//前尺度数2
                    table.Cell(i + 2, 6).Range.Text = ((double)dt1.Rows[j][15]).ToString();//高差2


                    table.Cell(i + 2, 7).Range.Text = ((double)dt1.Rows[j][13]).ToString();//读数差
                    table.Cell(i + 1, 8).Range.Text = ((double)dt1.Rows[j][17]).ToString();//高差
                    table.Cell(i + 1, 9).Range.Text = ((double)dt1.Rows[j][18]).ToString();//高程
                    table.Cell(i + 1, 10).Range.Text = ((double)dt1.Rows[j][18]).ToString();//平差后高程

                    table.Cell(i, 10).Merge(table.Cell(i + 2, 10));
                    i += 2;
                    j++;
                }
            }
            //设置table字体样式
            table.Rows.HeightRule = MSWord.WdRowHeightRule.wdRowHeightAtLeast;//列高度至少会是指定的最小值。
            table.Rows.Height = wordApp.CentimetersToPoints(float.Parse("0.8"));//将计量单位从厘米转换为磅（一磅等于 0.035 厘米）
            table.Range.Font.Size = 10.5F;
            table.Range.Font.Bold = 0;
            //设置table位置
            table.Rows.Alignment = WdRowAlignment.wdAlignRowCenter;//表格居中
            table.Range.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphCenter;//表格文本居中
            table.Range.Cells.VerticalAlignment = MSWord.WdCellVerticalAlignment.wdCellAlignVerticalCenter;//文本垂直贴到底部
            //设置table边框样式单线
            table.Borders.OutsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;
            table.Borders.InsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;
            table.Columns[1].Width = 30;//第一列宽度设为30
            table.Columns[2].Width = 65;//第一列宽度设为65
            table.Columns[3].Width = 50;//第一列宽度设为50
            table.Columns[4].Width = 50;//第一列宽度设为50
            //将其他列的宽度都设置为55
            for (int i = 5; i <= tableColumn; i++)
            {
                table.Columns[i].Width = 55;
            }
            //合并单元格
            table.Cell(1, 1).Merge(table.Cell(4, 1));//纵向合并
            table.Cell(1, 7).Merge(table.Cell(4, 7));
            table.Cell(1, 8).Merge(table.Cell(4, 8));
            table.Cell(1, 9).Merge(table.Cell(4, 9));
            table.Cell(1, 10).Merge(table.Cell(4, 10));
            table.Cell(1, 3).Merge(table.Cell(1, 4));//横向合并
            table.Cell(1, 4).Merge(table.Cell(1, 5));
        }

        public void CreatWorkExplain5(string brake,int year)
        {
            object path;//文件路径
            MSWord.Application wordApp;//Word应用程序变量
            MSWord.Document wordDoc;//Word文档变量
            path = "d:\\垂直位移量变化统计表.doc";//保存为Word2003文档
            // path = "d:\\myWord.doc";//保存为Word2007文档
            wordApp = new MSWord.ApplicationClass();//初始化
            object unite = Microsoft.Office.Interop.Word.WdUnits.wdStory;
            if (File.Exists((string)path))
            {
                File.Delete((string)path);
            }
            Object Nothing = Missing.Value;
            wordDoc = wordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);
            CZWYLBHTJB(unite, Nothing, wordApp, wordDoc, brake,year);
            //关闭保存word
            object format = MSWord.WdSaveFormat.wdFormatDocument;
            wordDoc.SaveAs(ref path, ref format, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
            wordDoc.Close(ref Nothing, ref Nothing, ref Nothing);
            wordApp.Quit(ref Nothing, ref Nothing, ref Nothing); //关闭wordApp组件对象
            DialogResult dr = MessageBox.Show("导出至D盘根目录成功！是否打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
            if (dr == DialogResult.Yes)
            {
                System.Diagnostics.Process.Start(path.ToString()); //打开此文件
            }
        }
        /// <summary>
        /// 垂直位移量变化统计表word设计
        /// </summary>
        /// <param name="unite"></param>
        /// <param name="Nothing"></param>
        /// <param name="wordApp"></param>
        /// <param name="wordDoc"></param>
        /// <param name="brake">闸站名</param>
        /// <param name="year">年份</param>
        public void CZWYLBHTJB(object unite, object Nothing, MSWord.Application wordApp, MSWord.Document wordDoc, string brake,int year)
        {
            object mymissing = System.Reflection.Missing.Value;
            object myunit = Microsoft.Office.Interop.Word.WdUnits.wdStory;
            object pBreak = (int)Microsoft.Office.Interop.Word.WdBreakType.wdPageBreak;
            //换页
            wordApp.Selection.EndKey(ref myunit, ref mymissing);
            //通过对日期排序得到首次观测日期，进而获取ID
            string sql0 = "select min(Date) from [cd_observation] where BrakeId = '" + brake + "'";
            System.Data.DataTable dt0 = GetDataTable(sql0);
            string sql00 = "select ID from [cd_observation] where BrakeId = '" + brake + "' and Date = '" + Convert.ToDateTime(dt0.Rows[0][0]) + "'";
            System.Data.DataTable dt00 = GetDataTable(sql00);
            //通过ID获取测点部位、编号，以及首次观测的日期（排序）
            string SQL = "select CdPart,CdId,Hit from [cd_result] where ID='" + dt00.Rows[0][0].ToString() + "' order by CdPart,CdId";
            System.Data.DataTable Dt = GetDataTable(SQL);
            //根据年份获得前五年的所有次观测及其ID
            ArrayList list = MyMeans.GetFiveYearsIdAndDate("cd_observation", "Date", year, "BrakeId", brake, "ID");
            string[] id = (string[])list[0];
            DateTime[] date = (DateTime[])list[1];
            double[,] hit = new double[Dt.Rows.Count, date.Length];
            for (int i = 0; i < date.Length; i++)
            {
                //根据ID获取高程（排序）
                string sql = "select Hit from [cd_result] where ID = '" + id[i] + "' order by CdPart,CdId";
                System.Data.DataTable dt = DbHelperSQL.GetDataTable(sql);
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    //计算累计位移量
                    hit[j, i] = Math.Round((Convert.ToDouble(dt.Rows[j][0]) - Convert.ToDouble(Dt.Rows[j][2]))*1000, 1);
                }
            }
            //生成横版
            Object oMissing = System.Reflection.Missing.Value;
            object WdLine = Microsoft.Office.Interop.Word.WdUnits.wdParagraph;
            object oPageBreak = Microsoft.Office.Interop.Word.WdBreakType.wdSectionBreakNextPage;//分页符
            wordApp.Selection.InsertBreak(ref oPageBreak);
            wordApp.Selection.MoveDown(WdLine, 2, oMissing);
            wordApp.Selection.InsertBreak(ref oPageBreak);
            wordApp.Selection.MoveUp(WdLine, 1, oMissing);
            wordApp.Selection.PageSetup.Orientation = Microsoft.Office.Interop.Word.WdOrientation.wdOrientLandscape;
            int tableRow = Dt.Rows.Count + 3;
            int tableColumn = date.Length + 2;
            //定义一个word中的表格对象
            MSWord.Table table = wordDoc.Tables.Add(wordApp.Selection.Range, tableRow, tableColumn, ref Nothing, ref Nothing);
            //表格的索引是从1开始的
            table.Cell(1, 1).Range.Text = brake + "垂直位移量变化统计表";
            table.Cell(2, 1).Range.Text = "测点";
            table.Cell(2, 3).Range.Text = "累计位移量(mm)";
            table.Cell(3, 1).Range.Text = "部位";
            table.Cell(3, 2).Range.Text = "编号";
            //写入第三行的日期
            for (int i = 3; i <= tableColumn; i++)
            {
                table.Cell(3, i).Range.Text = date[i - 3].ToLongDateString();
            }
            //写入累计位移量的值
            for (int i = 4; i <= tableRow; i++)
            {
                //写入前两列测点部位和编号
                table.Cell(i, 1).Range.Text = Dt.Rows[i - 4][0].ToString();
                table.Cell(i, 2).Range.Text = Dt.Rows[i - 4][1].ToString();
                for (int j = 3; j <= tableColumn; j++)
                {
                    table.Cell(i, j).Range.Text = hit[i - 4, j - 3].ToString();//累计位移量
                } 
            }
            //设置table字体样式
            table.Rows.HeightRule = MSWord.WdRowHeightRule.wdRowHeightAtLeast;//列高度至少会是指定的最小值。
            table.Rows.Height = wordApp.CentimetersToPoints(float.Parse("0.8"));//将计量单位从厘米转换为磅（一磅等于 0.035 厘米）
            table.Range.Font.Size = 10.5F;
            table.Range.Font.Bold = 0;
            //设置table位置
            table.Rows.Alignment = WdRowAlignment.wdAlignRowCenter;//表格居中
            table.Range.ParagraphFormat.Alignment = MSWord.WdParagraphAlignment.wdAlignParagraphCenter;//表格文本居中
            table.Range.Cells.VerticalAlignment = MSWord.WdCellVerticalAlignment.wdCellAlignVerticalCenter;//文本垂直贴到底部
            //设置table边框样式单线
            table.Borders.OutsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;
            table.Borders.InsideLineStyle = MSWord.WdLineStyle.wdLineStyleSingle;
            //设置前三行的行高
            table.Rows[1].Height = 55;
            table.Rows[2].Height = 35;
            table.Rows[3].Height = 65;
            table.Columns[1].Width = 45;//将第1列宽度设置为45
            table.Columns[2].Width = 35;
            //将其他列的宽度都设置为45
            for (int i = 3; i <= tableColumn; i++)
            {
                table.Columns[i].Width = 45;
            }
            table.Cell(1, 1).Range.Font.Size = 25F;
            table.Cell(1, 1).Range.Font.Bold = 1;
            //合并单元格
            table.Cell(1, 1).Merge(table.Cell(1, tableColumn));//横向合并
            table.Cell(2, 1).Merge(table.Cell(2, 2));
            table.Cell(2, 2).Merge(table.Cell(2, date.Length +1));
        }

        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataTable</returns>
        public System.Data.DataTable GetDataTable(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                System.Data.DataTable dt = new System.Data.DataTable();
                try
                {
                    SqlDataAdapter command = new SqlDataAdapter(sql, conn);
                    command.Fill(dt);
                }
                catch (SqlException ex)
                {

                    throw new Exception(ex.Message);
                }
                return dt;
            }
        }

    }
}
